
# coding: utf-8
from sqlalchemy import create_engine
import xlrd
import sys
import os,shutil
import math
import pandas as pd
mylist=[]
import datetime


myfile = sys.argv[1]
print (myfile)
#myfile ='e://123//79线路S2M-057A GPS数据.csv'
df1 = pd.read_csv(myfile, encoding='gbk')
df1['data']=pd.to_datetime(df1['发送时间.1'])
str1 = df1.ix[0, 1]
wq = "mysql+pymysql://mars:mars123@192.168.2.102:3322/sh_irs"

today = datetime.date.today()
oneday = datetime.timedelta(days=1)
yesterday = today - oneday


# # 数据同步检查

# In[57]:

def checkdb(val1, val2,val3):
    engine = create_engine(val1, max_overflow=3)
    connection = engine.connect()
    connection = connection.execution_options(
        isolation_level="READ COMMITTED"
    )
    sqlstr1='''select a.SJFCSJ,a.SJDDSJ from b_station a,b_channel b where a.b_channel_id= b.b_channel_id and a.bcrq ="''' \
            + str(val3) + '''"    and b.CLZBH ="''' + val2 +'" order by 1'
    rs = connection.execute(sqlstr1)
    print (rs)
    list2=[]
    for rc in rs:
        #print (rc)
        list2.append(rc)

    connection.close
    return list2


mylist=checkdb(wq, str1,yesterday)
df2=pd.DataFrame(mylist,columns=['aaa','bbb'])
df2['start'] = pd.to_datetime(df2['aaa'])
df2['stop'] = pd.to_datetime(df2['bbb'])
df2['diff'] = df2['stop'] - df2['start']
df2['res'] = df2['diff'].dt.total_seconds() / 10
#print (df2)
for index, row in df2.iterrows():
    #print (index)
    #print (row[2])
    #print (row[3])
    str3=df1[(df1.data >row[2]) & (df1.data <row[3])].count()[0]
    #print ('++++++++++++++')
    #print (str3)
    #print('------------------')
    df2.ix[index,'res3']=str3
    if df2.ix[index,'res']*0.9 < df2.ix[index,'res3']:
        df2.ix[index,'res4']='true'
    else:
        df2.ix[index, 'res4'] = 'false'

print ("\n")
print ("该车辆检查结果是___________________________")
print(df2.ix[:,[0,1,4,5,6,7]])






